<?php

/**
 * Trainer filter form.
 *
 * @package    sf_sandbox
 * @subpackage filter
 * @author     Your name here
 * @version    SVN: $Id: sfDoctrineFormFilterTemplate.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 */
class TrainerFormFilter extends BaseTrainerFormFilter {

    public function configure() {
        unset($this['name']);
        $this->widgetSchema['id'] = new sfWidgetFormDoctrineChoice(array('add_empty' => 'Select One', 'multiple' => false, 'model' => 'Trainer'));
        $this->validatorSchema['id'] = new sfValidatorPass();
    }

    public function getFields() {
        return array(
            'id' => 'Enum',
            'name' => 'Text',
        );
    }

    public function doBuildQueryForReport($values) {
        $q = parent::doBuildQuery($values);

        $q->innerJoin('r.Programs p');
//        if ($values['is_manager'] != '' || $values['intg_identity'] != '' || $values['location_of_holding'] != '' || (isset($values['code_sector_list']) && $values['code_sector_list'] != '') || (isset($values['code_target_list']) && $values['code_target_list'] != '') || $values['program_code']['text'] != '' || $values['trainer_id'] != '' || $values['is_mandatory'] != '') {
//        }

        if ($values['is_manager'] != '' || $values['intg_identity'] != '') {
            $q->innerJoin('r.School s');
            $q->innerJoin('s.Teachers teachers');
        }

        if ($values['program_code']['text'] != '') {
            $q->addWhere('p.program_code = ?', $values['program_code']['text']);
        }

        if ($values['location_of_holding'] != '') {
            $q->addWhere('p.location_of_holding = ?', $values['location_of_holding']);
        }

        if ($values['trainer_id'] != '') {
            $q->addWhere('p.trainer_id = ?', $values['trainer_id']);
        }

        if ($values['is_mandatory'] != '') {
            $q->addWhere('p.is_mandatory = ?', $values['is_mandatory']);
        }

        if (isset($values['code_target_list']) && $values['code_target_list'] != '') {
            $q->innerJoin('p.CodeTarget ct');
            $q->whereIn('ct.id', $values['code_target_list']);
        }

        if (isset($values['code_sector_list']) && $values['code_sector_list'] != '') {
            $q->innerJoin('p.CodeSector cs');
            $q->whereIn('cs.intg_id', $values['code_sector_list']);
        }

        if ($values['school_intg_id'] != '') {
            $q->whereIn('r.school_intg_id', $values['school_intg_id']);
        }

        if ($values['is_manager'] != '') {
            $q->addWhere('teachers.is_manager = ?', $values['is_manager']);
        }

        if ($values['intg_identity'] != '') {
            $q->addWhere('teachers.intg_identity = ?', $values['intg_identity']);
        }

        if ($values['trainer_id'] != '') {
            $q->addWhere('r.id = ?', $values['trainer_id']);
        }

        $q->leftJoin('p.LocationsInSector loc');
        $q->select('CONCAT(r.f_name," ",r.s_name," ",r.t_name," ",r.l_name) as trainer_name ,r.id, p.id as prog_id, p.name as pname, p.start_date as start_date, p.end_date as end_date, loc.name as location');
        $q->addSelect('p.duration_hours as duration_hours, (SELECT COUNT(pp.id) from Program pp, LinkTeacherProgram link2 WHERE link2.program_id = pp.id AND link2.assign_status = 2 AND pp.id = p.id) as assignee_count');
        $q->orderBy('r.f_name ASC');
        return $q;
    }

}
